Arbeitspaket (AP) 3: Management & Nutzung Räumliche Daten¶
Angaben Studierende(r) (fehlende Angaben ergänzen)¶
| Vorname: | Micola |
| Nachname: | Vital |
| Immatrikulationsnummer: | 20267217 |
| Modul: | Data Science |
| Prüfungsdatum / Raum / Zeit: | 07.10.2024 / Raum: SF O3.54 / 8:00 – 11:45 |
| Erlaubte Hilfsmittel: | w.MA.XX.DS.24HS (Data Science) Open Book, Eigener Computer, Internet-Zugang |
| Nicht erlaubt: | Nicht erlaubt ist der Einsatz beliebiger Formen von generativer KI (z.B. Copilot, ChatGPT) sowie beliebige Formen von Kommunikation oder Kollaboration mit anderen Menschen. |
Bewertungskriterien¶
(max. erreichbare Punkte: 48)¶
| Kategorie | Beschreibung | Punkteverteilung |
|---|---|---|
| Code nicht lauffähig oder Ergebnisse nicht sinnvoll | Der Code enthält Fehler, die verhindern, dass er ausgeführt werden kann (z.B. Syntaxfehler) oder es werden Ergebnisse ausgegeben, welche nicht zur Fragestellung passen. | 0 Punkte |
| Code lauffähig, aber mit gravierenden Mängeln | Der Code läuft, aber die Ergebnisse sind aufgrund wesentlicher Fehler unvollständig (z.B. fehlende Joins, gravierende Fehler in SQL-Abfragen). Nur geringer Fortschritt erkennbar. | 25% der max. erreichbaren Punkte |
| Code lauffähig, aber mit mittleren Mängeln | Der Code läuft und liefert teilweise korrekte Ergebnisse, aber es gibt grössere Fehler (z.B. fehlende Spalten, unvollständige SQL-Abfragen). Die Ergebnisse sind nachvollziehbar, aber unvollständig oder ungenau. | 50% der max. erreichbaren Punkte |
| Code lauffähig, aber mit minimalen Mängeln | Der Code läuft und liefert ein weitgehend korrektes Ergebnis, aber kleinere Fehler (z.B. falsche oder fehlende Sortierung, Rundung von Werten falsch) beeinträchtigen die Vollständigkeit des Ergebnisses. | 75% der max. erreichbaren Punkte |
| Code lauffähig und korrekt | Der Code läuft einwandfrei und liefert das korrekte Ergebnis ohne Mängel. | 100% der max. erreichbaren Punkte |
Python Libraries und Settings¶
In [139]:
# Libraries
import os
import folium
import pandas as pd
import geopandas as gpd
from sqlalchemy import create_engine, text
# Ignore warnings
import warnings
warnings.filterwarnings("ignore")
print(os.getcwd())
/workspaces/python_postgresql_postgis
Vorbereitung (Hinweis: dieser Teil wird nicht bewertet)¶
1.) Starten Sie eine GitHub Codespaces Instanz auf Basis Ihres Forks des folgenden GitHub Repositories:¶
GitHub-Repository: https://github.com/mario-gellrich-zhaw/python_postgresql_postgis¶
WICHTIG!!! Verwenden Sie eine GitHub Codespaces Instanz mit ausreichend Arbeitsspeicher (4core, 16GB RAM).¶
Hinweis:
- Im Unterricht wurden bereits sämtliche Installationen und Einstellungen inkl. der Registrierung des Datenbank Servers auf pgAdmin vorgenommen.
- Falls Sie die Codespaces-Instanz neu erstellen müssen, folgen Sie bitte den detaillierten Erklärungen auf der README-Seite des GitHub Repositories.
2.) Erstellen und Testen Sie die Datenbankverbindung mit der 'osm_switzerland' Datenbank.¶
In [140]:
# Set up Database Connection
user = "pgadmin"
password = "geheim"
host = "localhost"
port = "5432"
database = "osm_switzerland"
# Erstellen der Connection URL
db_connection_url = "postgresql://" + user + ":" + password +\
"@" + host + ":" + port + "/" + database
# Erstellen SQLAlchemy Engine
engine = create_engine(db_connection_url)
# Test der Connection
with engine.connect() as connection:
result = connection.execute(text('SELECT current_database()'))
print(result.fetchone())
# Verbindung trennen
engine.dispose()
('osm_switzerland',)
Aufgaben (Dieser Teil wird bewertet!)¶
Hinweise zu den folgenden Aufgabenstellungen:
- In diesem Jupyter Notebook gibt es jeweils zwei Code-Zellen pro Aufgabe:
- Eine Codezelle mit Python-Code und einem SQL-Statement für die Datenbank-Abfrage.
- Eine Codezelle mit Python-Code für die Kartendarstellung der Ergebnisse der jeweiligen SQL-Abfrage.
- In den Codezellen für die Datenbank-Abfrage muss jeweils das SQL-Statement ergänzt werden.
- In den Codezellen für die Kartendarstellung muss nur dann der Python Code ergänzt werden, wenn in der Aufgabe danach gefragt wird.
Aufgabe (1): Erstellen Sie eine Abfrage sämtlicher Autoreparatur-Werkstätten in der Schweiz¶
Details zur Aufgabenstellung:
- Sie finden die benötigten Daten in der Tabelle 'planet_osm_point'.
- Stellen sie in der Ergebnistabelle die Spalten: osm_id, shop sowie die transformierte Geometrie als Spalte geom dar.
- Tipp: Die Geometry wird mit Hilfe der Funktion st_transform() transformiert, z.B.: st_transform(p.way, 4326) AS geom.
- Tipp: Autoreparatur-Werkstätten sind mit dem key:value Paar shop='car_repair' in der OpenStreetMap Map-Feature Übersicht angegeben.
- vgl: https://wiki.openstreetmap.org/wiki/Map_features
(max. erreichbare Punkte: 6)
In [141]:
# Engine für Datenbankverbindung erstellen
engine = create_engine(db_connection_url)
# Ergänzen Sie die SQL-Abfrage, um die Aufgabe zu lösen
sql = """SELECT
h.osm_id,
h.shop,
ST_Transform(h.way, 4326) AS geom
FROM planet_osm_point h
WHERE h.shop = 'car_repair';"""
# Ergebnis in GeoDataFrame abspeichern
gdf = gpd.GeoDataFrame.from_postgis(sql, engine)
# Datenbankverbindung trennen
engine.dispose()
# Zeigen des GeoDataFrames
gdf
Out[141]:
| osm_id | shop | geom | |
|---|---|---|---|
| 0 | 1811755810 | car_repair | POINT (9.62898 47.45412) |
| 1 | 9408250312 | car_repair | POINT (9.63098 47.45327) |
| 2 | 5254765356 | car_repair | POINT (9.43594 47.50471) |
| 3 | 9408188280 | car_repair | POINT (9.46826 47.49408) |
| 4 | 3346119599 | car_repair | POINT (9.49096 47.47737) |
| ... | ... | ... | ... |
| 1459 | 4171944125 | car_repair | POINT (9.16975 47.61128) |
| 1460 | 4171944126 | car_repair | POINT (9.1698 47.60761) |
| 1461 | 7077905514 | car_repair | POINT (9.2903 47.6054) |
| 1462 | 7077905513 | car_repair | POINT (9.28444 47.60599) |
| 1463 | 1492440172 | car_repair | POINT (9.20147 47.63996) |
1464 rows × 3 columns
Kartendarstellung Ergebnis (nur anpassen, falls in der Aufgabe danach gefragt wird)¶
In [142]:
# Projektion definieren (WGS84)
if gdf.crs is None:
gdf.set_crs(epsg=4326, inplace=True)
else:
pass
# Latitude und Longitude für die Zentrierung der Karte ermitteln
centroids = gdf.geometry.centroid
lon = centroids.x.mean()
lat = centroids.y.mean()
# Initialisieren der Map
m = folium.Map(location=[lat, lon],
zoom_start=8,
tiles='CartoDB positron')
# Map settings
folium.GeoJson(
gdf,
name='map'
).add_to(m)
folium.LayerControl().add_to(m)
# Plot map
m
Out[142]:
Make this Notebook Trusted to load map: File -> Trust Notebook
Aufgabe (2) Erstellen Sie eine Abfrage aller Biergärten in der Schweiz.¶
Details zur Aufgabenstellung:
- Sie finden die benötigten Daten in den Tabellen 'planet_osm_point'.
- Stellen Sie in der Ergebnistabelle die Spalten: osm_id, amenity, name und die transformierte Geometrie als Spalte geom dar.
- Tipp: Biergärten sind mit dem key:value Paar amenity='biergarten' in der OpenStreetMap Map-Feature Übersicht angegeben.
- vgl: https://wiki.openstreetmap.org/wiki/Map_features
(max. erreichbare Punkte: 6)
In [143]:
# Engine für Datenbankverbindung erstellen
engine = create_engine(db_connection_url)
# Ergänzen Sie die SQL-Abfrage, um die Aufgabe zu lösen
sql = """SELECT
h.osm_id,
h.amenity,
h.name,
ST_Transform(h.way, 4326) AS geom
FROM planet_osm_point h
WHERE h.amenity = 'biergarten';"""
# Ergebnis in GeoDataFrame abspeichern
gdf = gpd.GeoDataFrame.from_postgis(sql, engine)
# Datenbankverbindung trennen
engine.dispose()
# Zeigen des GeoDataFrames
gdf.head()
Out[143]:
| osm_id | amenity | name | geom | |
|---|---|---|---|---|
| 0 | 704467869 | biergarten | Bierhalle | POINT (9.6068 47.40694) |
| 1 | 370319479 | biergarten | Center da Surf | POINT (9.79135 46.45518) |
| 2 | 2725618744 | biergarten | La Bulle | POINT (7.10772 45.92965) |
| 3 | 648260667 | biergarten | None | POINT (7.38983 46.12865) |
| 4 | 2991096399 | biergarten | Stierentungel-Beizli | POINT (7.37019 46.39482) |
Kartendarstellung Ergebnis (nur anpassen, falls in der Aufgabe danach gefragt wird)¶
In [144]:
# Projektion definieren (WGS84)
if gdf.crs is None:
gdf.set_crs(epsg=4326, inplace=True)
else:
pass
# Latitude und Longitude für die Zentrierung der Karte ermitteln
centroids = gdf.geometry.centroid
lon = centroids.x.mean()
lat = centroids.y.mean()
# Initialisieren der Map
m = folium.Map(location=[lat, lon],
zoom_start=8,
tiles='CartoDB positron')
# Map settings
folium.GeoJson(
gdf,
name='map'
).add_to(m)
folium.LayerControl().add_to(m)
# Plot map
m
Out[144]:
Make this Notebook Trusted to load map: File -> Trust Notebook
Aufgabe (3): Erstellen Sie eine Abfrage aller Gebäude in der Stadthausstrasse in Winterthur, welche vollständige Adressangaben besitzen.¶
Details zur Aufgabenstellung:
- Sie finden die benötigten Daten in der Tabelle 'planet_osm_polygon'.
- Vollständige Adressangabe bedeutet: Strassenname, Haunummer, PLZ, Gemeindename sind vorhanden.
- Stellen Sie in der Ergebnistabelle sämtliche Adressangaben sowie die transformierte Geometrie als Spalte geom dar.
- Verwenden Sie für die Darstellung als Hintergrundkarte ein Satellitenbild (ESRIWorldImagery) als maptile.
- Tipp: Gebäude sind in der Spalte 'building' klassifiziert. Mit WHERE building IS NOT NULL können Sie Gebäude filtern.
(max. erreichbare Punkte: 6)
In [145]:
# Engine für Datenbankverbindung erstellen
engine = create_engine(db_connection_url)
# Ergänzen Sie die SQL-Abfrage, um die Aufgabe zu lösen
sql = """SELECT
p.osm_id,
p."addr:street",
p."addr:housenumber",
p."addr:city",
p."addr:postcode",
p.building,
st_transform(p.way, 4326) AS geom
FROM
public.planet_osm_polygon AS p
WHERE
p."addr:street" IS NOT NULL
AND p."addr:housenumber" IS NOT NULL
AND p."addr:city" IS NOT NULL
AND p."addr:postcode" IS NOT NULL
AND p."addr:city" = 'Winterthur'
AND p."addr:postcode" IN ('8400')
AND p."addr:street" = 'Stadthausstrasse';"""
# Ergebnis in GeoDataFrame abspeichern
gdf = gpd.GeoDataFrame.from_postgis(sql, engine)
# Datenbankverbindung trennen
engine.dispose()
# Zeigen des GeoDataFrames
gdf
Out[145]:
| osm_id | addr:street | addr:housenumber | addr:city | addr:postcode | building | geom | |
|---|---|---|---|---|---|---|---|
| 0 | 134980581 | Stadthausstrasse | 10b | Winterthur | 8400 | yes | POLYGON ((8.72652 47.50075, 8.72661 47.50063, ... |
| 1 | 9264543 | Stadthausstrasse | 4a | Winterthur | 8400 | government | POLYGON ((8.73111 47.50115, 8.73112 47.50085, ... |
| 2 | 22301937 | Stadthausstrasse | 4 | Winterthur | 8400 | apartments | POLYGON ((8.73232 47.50109, 8.73233 47.50107, ... |
| 3 | 75027485 | Stadthausstrasse | 31 | Winterthur | 8400 | office | POLYGON ((8.73162 47.50041, 8.73165 47.50029, ... |
| 4 | 75027472 | Stadthausstrasse | 35 | Winterthur | 8400 | apartments | POLYGON ((8.73153 47.5004, 8.73157 47.50026, 8... |
| ... | ... | ... | ... | ... | ... | ... | ... |
| 56 | 188158695 | Stadthausstrasse | 113 | Winterthur | 8400 | apartments | POLYGON ((8.72702 47.4999, 8.72706 47.49975, 8... |
| 57 | 94283231 | Stadthausstrasse | 145 | Winterthur | 8400 | apartments | POLYGON ((8.72414 47.4995, 8.72417 47.49939, 8... |
| 58 | 94283304 | Stadthausstrasse | 143 | Winterthur | 8400 | office | POLYGON ((8.72427 47.49952, 8.72431 47.4994, 8... |
| 59 | 24804763 | Stadthausstrasse | 22 | Winterthur | 8400 | office | POLYGON ((8.72448 47.49982, 8.72453 47.4997, 8... |
| 60 | 26992511 | Stadthausstrasse | 24 | Winterthur | 8400 | retail | POLYGON ((8.72405 47.49972, 8.72407 47.49967, ... |
61 rows × 7 columns
Kartendarstellung Ergebnis (nur anpassen, falls in der Aufgabe danach gefragt wird)¶
In [146]:
# Projektion definieren (WGS84)
if gdf.crs is None:
gdf.set_crs(epsg=4326, inplace=True)
else:
pass
# Latitude und Longitude für die Zentrierung der Karte ermitteln
centroids = gdf.geometry.centroid
lon = centroids.x.mean()
lat = centroids.y.mean()
# Initialisieren der Map
m = folium.Map(location=[lat, lon],
zoom_start=17,
tiles='ESRIWorldImagery')
# Map settings
folium.Choropleth(
geo_data=gdf,
name='map',
fill_color='greenyellow'
).add_to(m)
folium.LayerControl().add_to(m)
# Plot map
m
Out[146]:
Make this Notebook Trusted to load map: File -> Trust Notebook
Aufgabe (4): Erstellen Sie eine Abfrage aller Strassen in der Schweiz, welche als 'motorway' klassifiziert sind.¶
Details zur Aufgabenstellung:
- Sie finden die benötigten Informationen in der Tabelle 'planet_osm_roads'.
- Stellen Sie in der Ergebnistabelle die Spalten: osm_id, highway und die transformierte Geometrie als Spalte geom dar.
- Tipp: Motorways sind mit dem key:value Paar highway='motorway' in der OpenStreetMap Map-Feature Übersicht angegeben.
- vgl: https://wiki.openstreetmap.org/wiki/Map_features
(max. erreichbare Punkte: 6)
In [147]:
# Engine für Datenbankverbindung erstellen
engine = create_engine(db_connection_url)
# Ergänzen Sie die SQL-Abfrage, um die Aufgabe zu lösen
sql = """SELECT
h.osm_id,
h.highway,
ST_Transform(h.way, 4326) AS geom
FROM planet_osm_roads h
WHERE h.highway = 'motorway';"""
# Ergebnis in GeoDataFrame abspeichern
gdf = gpd.GeoDataFrame.from_postgis(sql, engine)
# Datenbankverbindung trennen
engine.dispose()
# Zeigen des GeoDataFrames
gdf
Out[147]:
| osm_id | highway | geom | |
|---|---|---|---|
| 0 | 1236416666 | motorway | LINESTRING (9.64218 47.43433, 9.6422 47.43402,... |
| 1 | 552469430 | motorway | LINESTRING (9.64232 47.43412, 9.6423 47.43469) |
| 2 | 552469432 | motorway | LINESTRING (9.6423 47.43469, 9.64232 47.43504,... |
| 3 | 552469428 | motorway | LINESTRING (9.64312 47.43793, 9.6429 47.43753,... |
| 4 | 186132194 | motorway | LINESTRING (9.64235 47.43533, 9.6424 47.43568,... |
| ... | ... | ... | ... |
| 8263 | 137477250 | motorway | LINESTRING (9.15877 47.65537, 9.15878 47.65526... |
| 8264 | 100501714 | motorway | LINESTRING (9.15428 47.65126, 9.15442 47.65142... |
| 8265 | 100501659 | motorway | LINESTRING (9.15415 47.6513, 9.15396 47.65108,... |
| 8266 | 137477248 | motorway | LINESTRING (9.15384 47.65071, 9.15395 47.65087... |
| 8267 | 100501651 | motorway | LINESTRING (9.15834 47.65468, 9.15789 47.65424... |
8268 rows × 3 columns
Kartendarstellung Ergebnis (nur anpassen, falls in der Aufgabe danach gefragt wird)¶
In [148]:
# Projektion definieren (WGS84)
if gdf.crs is None:
gdf.set_crs(epsg=4326, inplace=True)
else:
pass
# Latitude und Longitude für die Zentrierung der Karte ermitteln
centroids = gdf.geometry.centroid
lon = centroids.x.mean()
lat = centroids.y.mean()
# Initialisieren der Map
m = folium.Map(location=[lat, lon],
zoom_start=9,
tiles='CartoDB positron')
# Map settings
folium.Choropleth(
geo_data=gdf,
name='map',
line_weight=3,
line_color='red'
).add_to(m)
folium.LayerControl().add_to(m)
# Plot map
m
Out[148]:
Make this Notebook Trusted to load map: File -> Trust Notebook
Aufgabe (5): Erstellen Sie eine Abfrage aller Schweizer Flüsse. Generieren Sie zusätzlich Buffer um die Flüsse mit einer Breite von 2000m.¶
Details zur Aufgabenstellung:
- Sie finden die benötigten Informationen in der Tabelle 'planet_osm_line'.
- Stellen Sie in der Ergebnistabelle die Spalten: osm_id, waterway sowie die transformierte Geometrie als Spalte geom dar.
- Tipp: Flüsse sind mit dem key:value Paar waterway='river' in der OpenStreetMap Map-Feature Übersicht angegeben.
- vgl: https://wiki.openstreetmap.org/wiki/Map_features
- Tipp: Per Default wird für jedes Fluss-Segment ein separater Buffer erstellt. Es ist nicht notwendig daraus einen einzelnen Buffer zu generieren.
(max. erreichbare Punkte: 8)
In [149]:
# Engine für Datenbankverbindung erstellen
engine = create_engine(db_connection_url)
# Ergänzen Sie die SQL-Abfrage, um die Aufgabe zu lösen
sql = """SELECT
p.osm_id,
p.waterway,
1 as group_id,
ST_TRANSFORM(ST_UNION(ST_Buffer(p.way::geometry, 2000)), 4326) AS geom
FROM public.planet_osm_line AS p
WHERE
waterway = 'river'
group by p.osm_id, p.waterway;"""
# Ergebnis in GeoDataFrame abspeichern
gdf = gpd.GeoDataFrame.from_postgis(sql, engine)
# Datenbankverbindung trennen
engine.dispose()
# Zeigen des GeoDataFrames
gdf
Out[149]:
| osm_id | waterway | group_id | geom | |
|---|---|---|---|---|
| 0 | 4223448 | river | 1 | POLYGON ((7.61135 46.75204, 7.61112 46.75215, ... |
| 1 | 4224616 | river | 1 | POLYGON ((7.62089 46.74728, 7.62027 46.74748, ... |
| 2 | 4245442 | river | 1 | POLYGON ((8.61599 46.8591, 8.61509 46.86111, 8... |
| 3 | 4245448 | river | 1 | POLYGON ((8.62089 46.82734, 8.62063 46.82792, ... |
| 4 | 4254242 | river | 1 | POLYGON ((8.53617 47.55367, 8.53615 47.55396, ... |
| ... | ... | ... | ... | ... |
| 2552 | 1317015786 | river | 1 | POLYGON ((8.74907 46.50162, 8.75252 46.50114, ... |
| 2553 | 1317015787 | river | 1 | POLYGON ((8.73866 46.50069, 8.73864 46.50073, ... |
| 2554 | 1318731230 | river | 1 | POLYGON ((6.00715 46.18904, 6.00742 46.1891, 6... |
| 2555 | 1319653743 | river | 1 | POLYGON ((10.11183 46.68295, 10.11115 46.68303... |
| 2556 | 1319653744 | river | 1 | POLYGON ((10.10057 46.68987, 10.09988 46.69099... |
2557 rows × 4 columns
Kartendarstellung Ergebnis (nur anpassen, falls in der Aufgabe danach gefragt wird)¶
In [150]:
# Projektion definieren (WGS84)
if gdf.crs is None:
gdf.set_crs(epsg=4326, inplace=True)
else:
pass
# Latitude und Longitude für die Zentrierung der Karte ermitteln
centroids = gdf.geometry.centroid
lon = centroids.x.mean()
lat = centroids.y.mean()
# Initialisieren der Map
m = folium.Map(location=[lat, lon],
zoom_start=8,
tiles='CartoDB positron')
# Map settings
folium.Choropleth(
geo_data=gdf,
name='map',
fill_color='greenyellow'
).add_to(m)
folium.LayerControl().add_to(m)
# Plot map
m
Out[150]:
Make this Notebook Trusted to load map: File -> Trust Notebook
Aufgabe (6): Erstellen Sie eine Abfrage der Bäckerei-Geschäfte in Zürich und Winterthur.¶
Details zur Aufgabenstellung:
- Sie finden die benötigten Daten in den Tabellen 'planet_osm_point' (Backereien).
- Verwenden Sie die Städtenamen aus den Adressangaben für die Abfrage der Bäckerei-Standorte (Zürich, Winterthur).
- Stellen Sie in der Ergebnistabelle die Spalten: osm_id, shop, name, "addr:city" sowie die transformierte Geometrie als geom dar.
- Wählen Sie eine Satelliten Karte von ESRI als Hintergrundkarte (maptile).
- Sortieren Sie die Bäckerei-Geschäfte aufsteigend nach osm_id.
- Tipp: Bäckerei-Geschäfte sind mit dem key:value Paar shop='bakery' in der OpenStreetMap Map-Feature Übersicht angegeben.
- vgl: https://wiki.openstreetmap.org/wiki/Map_features
(max. erreichbare Punkte: 8)
In [151]:
# Engine für Datenbankverbindung erstellen
engine = create_engine(db_connection_url)
# Ergänzen Sie die SQL-Abfrage, um die Aufgabe zu lösen
sql = """SELECT
h.osm_id,
h.shop,
h.name,
h."addr:city",
ST_Transform(h.way, 4326) AS geom
FROM planet_osm_point h
WHERE h.shop = 'bakery'
AND h."addr:city" IN ('Zürich', 'Winterthur')
ORDER BY h.osm_id ASC;"""
# Ergebnis in GeoDataFrame abspeichern
gdf = gpd.GeoDataFrame.from_postgis(sql, engine)
# Datenbankverbindung trennen
engine.dispose()
# Zeigen des GeoDataFrames
gdf
Out[151]:
| osm_id | shop | name | addr:city | geom | |
|---|---|---|---|---|---|
| 0 | 252457457 | bakery | Steiner Flughafebeck | Zürich | POINT (8.49779 47.40311) |
| 1 | 266630770 | bakery | Brezelkönig | Zürich | POINT (8.48875 47.39147) |
| 2 | 267879346 | bakery | Walter Buchmann | Zürich | POINT (8.51892 47.36239) |
| 3 | 268602152 | bakery | Moser's | Zürich | POINT (8.54945 47.3632) |
| 4 | 270794699 | bakery | John Baker | Zürich | POINT (8.56645 47.36493) |
| ... | ... | ... | ... | ... | ... |
| 101 | 10082330824 | bakery | RAM3 | Winterthur | POINT (8.74193 47.49395) |
| 102 | 10884168277 | bakery | Juliette | Zürich | POINT (8.5328 47.36658) |
| 103 | 10946043353 | bakery | Babu's Bakery | Zürich | POINT (8.51568 47.37473) |
| 104 | 10955852823 | bakery | Täglich Brot | Zürich | POINT (8.50705 47.36079) |
| 105 | 11951988149 | bakery | Wagner | Zürich | POINT (8.51763 47.3698) |
106 rows × 5 columns
Kartendarstellung Ergebnis (nur anpassen, falls in der Aufgabe danach gefragt wird)¶
In [152]:
# Projektion definieren (WGS84)
if gdf.crs is None:
gdf.set_crs(epsg=4326, inplace=True)
else:
pass
# Latitude und Longitude für die Zentrierung der Karte ermitteln
centroids = gdf.geometry.centroid
lon = centroids.x.mean()
lat = centroids.y.mean()
# Initialisieren der Map
m = folium.Map(location=[lat, lon],
zoom_start=12,
tiles='EsriWorldImagery')
# Map settings
folium.GeoJson(
gdf,
name='map',
).add_to(m)
folium.LayerControl().add_to(m)
# Plot map
m
Out[152]:
Make this Notebook Trusted to load map: File -> Trust Notebook
Aufgabe (7): Erstellen Sie eine Abfrage sämtlicher Coiffeur-Geschäfte in einem Radius von 500m um den Hauptbahnhof in Zürich.¶
Details zur Aufgabenstellung:
- Sie finden die Daten in der Tabelle 'planet_osm_point'.
- Berechnen Sie in der Abfrage die Distanz jedes Coiffeur-Geschäfts zum Hauptbahnhof in Metern als Spalte 'distance_meters'.
- Stellen Sie in der Ergebnistabelle die Spalten: osm_id, shop, name, distance_meters, sowie die transformierte Geometrie als geom dar.
- Wählen Sie eine Satelliten Karte von ESRI als Hintergrundkarte (maptile).
- Integrieren Sie in die Kartendarstellung den Namen (Spalte 'name') der Coiffeur-Geschäfte als Popup.
- Tipp: Coiffeur-Geschäfte sind mit dem key:value Paar shop='hairdresser' in der OpenStreetMap Map-Feature Übersicht angegeben.
- vgl: https://wiki.openstreetmap.org/wiki/Map_features
(max. erreichbare Punkte: 8)
In [153]:
# Engine für Datenbankverbindung erstellen
engine = create_engine(db_connection_url)
# Ergänzen Sie die SQL-Abfrage, um die Aufgabe zu lösen
sql = """SELECT
p.osm_id,
p.shop,
p.name,
ST_Distance(
ST_Transform(p.way, 4326)::geography,
-- Central station coordinates
ST_SetSRID(ST_MakePoint(8.53936, 47.3781), 4326)::geography
) AS distance_meters,
ST_TRANSFORM(p.way, 4326) AS geom
FROM
planet_osm_point AS p
WHERE
p.shop = 'hairdresser'
AND ST_DWithin(
ST_Transform(p.way, 4326)::geography,
-- Central station coordinates
ST_SetSRID(ST_MakePoint(8.53936, 47.3781), 4326)::geography,
500
);"""
# Ergebnis in GeoDataFrame abspeichern
gdf = gpd.GeoDataFrame.from_postgis(sql, engine)
# Datenbankverbindung trennen
engine.dispose()
# Zeigen des GeoDataFrames
gdf.head()
Out[153]:
| osm_id | shop | name | distance_meters | geom | |
|---|---|---|---|---|---|
| 0 | 11758825735 | hairdresser | MSH Salon | 361.728635 | POINT (8.5424 47.38062) |
| 1 | 1357158512 | hairdresser | Coiffeur-Studio Silvia Baumgartner | 310.980273 | POINT (8.54214 47.38017) |
| 2 | 4833061593 | hairdresser | McCoiffure | 226.319141 | POINT (8.53695 47.37931) |
| 3 | 4424939218 | hairdresser | McCoiffure | 111.635316 | POINT (8.53791 47.37791) |
| 4 | 4244059289 | hairdresser | Art Coiffure Kaiser | 133.439602 | POINT (8.53788 47.37745) |
Kartendarstellung Ergebnis (nur anpassen, falls in der Aufgabe danach gefragt wird)¶
In [154]:
# Projektion definieren (WGS84)
if gdf.crs is None:
gdf.set_crs(epsg=4326, inplace=True)
else:
pass
# Latitude und Longitude für die Zentrierung der Karte ermitteln
centroids = gdf.geometry.centroid
lon = centroids.x.mean()
lat = centroids.y.mean()
# Initialisieren der Map
m = folium.Map(location=[lat, lon],
zoom_start=16,
tiles='EsriWorldImagery')
# Map settings
folium.GeoJson(
gdf,
name='map',
popup=folium.GeoJsonPopup(fields=['name']) # popups anpassen gemäss Spalten, welche zuvor ausgegeben werden
).add_to(m)
folium.LayerControl().add_to(m)
# Plot map
m
Out[154]:
Make this Notebook Trusted to load map: File -> Trust Notebook
Jupyter notebook --footer info-- (please always provide this at the end of each notebook)¶
In [155]:
import os
import platform
import socket
from platform import python_version
from datetime import datetime
print('-----------------------------------')
print(os.name.upper())
print(platform.system(), '|', platform.release())
print('Datetime:', datetime.now().strftime("%Y-%m-%d %H:%M:%S"))
print('Python Version:', python_version())
print('IP Address:', socket.gethostbyname(socket.gethostname()))
print('-----------------------------------')
----------------------------------- POSIX Linux | 6.5.0-1025-azure Datetime: 2024-10-07 09:29:13 Python Version: 3.12.1 IP Address: 127.0.0.1 -----------------------------------